const sqlite3 = require('sqlite3').verbose();
const path = require('path');
const fs = require('fs');

// 确保数据库文件目录存在
const dbPath = path.join(__dirname, 'images.db');
const db = new sqlite3.Database(dbPath, (err) => {
    if (err) {
        console.error('数据库连接失败:', err.message);
        return;
    }
    console.log('已连接到SQLite数据库');
    
    // 初始化图片表（如果不存在）
    db.run(`
        CREATE TABLE IF NOT EXISTS images (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            originalname TEXT,
            filename TEXT,
            mimetype TEXT,
            size INTEGER,
            uploadTime TEXT
        )
    `);
});

// 插入图片信息到数据库
function insertImage(imageInfo) {
    return new Promise((resolve, reject) => {
        const sql = `
            INSERT INTO images (originalname, filename, mimetype, size, uploadTime)
            VALUES (?, ?, ?, ?, ?)
        `;
        const params = [
            imageInfo.originalname,
            imageInfo.filename,
            imageInfo.mimetype,
            imageInfo.size,
            imageInfo.uploadTime
        ];
        db.run(sql, params, function(err) {
            if (err) {
                reject(err);
                return;
            }
            resolve({ id: this.lastID, ...imageInfo });
        });
    });
}

// 查询所有图片
function getImages() {
    return new Promise((resolve, reject) => {
        db.all(`SELECT * FROM images ORDER BY uploadTime DESC`, (err, rows) => {
            if (err) {
                reject(err);
                return;
            }
            resolve(rows);
        });
    });
}

// 删除图片（数据库记录 + 物理文件）
function deleteImage(id, filename) {
    return new Promise((resolve, reject) => {
        // 先删数据库记录
        db.run(`DELETE FROM images WHERE id = ? AND filename = ?`, [id, filename], (err) => {
            if (err) {
                reject(err);
                return;
            }
            // 再删物理文件
            const filePath = path.join(__dirname, 'files', filename);
            if (fs.existsSync(filePath)) {
                fs.unlinkSync(filePath);
            }
            resolve({ success: true });
        });
    });
}

module.exports = {
    insertImage,
    getImages,
    deleteImage
};